package com.hgj.dao.impl;

import com.hgj.dao.CommentDao;
import com.hgj.entity.Comment;
import com.hgj.entity.User;
import com.hgj.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CommentDaoImpl implements CommentDao {
    //1.获取连接
    Connection conn=DBUtil.getConnection();
    //2.创建执行sql工具类对象
    QueryRunner qr=new QueryRunner();
    @Override
    public int save(Comment obj) {
        //3.定义sql语句
        String sql="INSERT INTO  tb_comment(comment_id,user_id,goods_id,comment_date,comment_content,comment_type) VALUES (?,?,?,?,?,?)";
        //4.传递参数是一个数组 按照占位符顺序传参
        Object[] paras={
                obj.getComment_id(),
                obj.getUser_id(),
                obj.getGoods_id(),
                obj.getComment_date(),
                obj.getComment_content(),
                obj.getComment_type()
        };
        //5.更新到数据库操作
        //update(连接,sql语句,参数)
        try {
            int row=qr.update(conn,sql,paras);
            return  row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(Comment obj) {
        //3.定义sql语句
        String sql="UPDATE  tb_comment SET  user_id=?,goods_id=?,comment_date=?,comment_content=?,comment_type=? WHERE comment_id=?  ";
        //4.传递参数是一个数组 按照占位符顺序传参
        Object[] paras={
                obj.getComment_id(),
                obj.getUser_id(),
                obj.getGoods_id(),
                obj.getComment_date(),
                obj.getComment_content(),
                obj.getComment_type()
        };
        //5.更新到数据库操作
        //update(连接,sql语句,参数)
        try {
            int row=qr.update(conn,sql,paras);
            return  row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(Object id) {
        String sql="DELETE FROM tb_comment WHERE comment_id  = ? ";
        Object[] paras={id};
        try {
            int row=qr.update(conn,sql,paras);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Comment findById(Object id) {
        String sql="select * from tb_comment " +
                "  where commnet_id=?";
        Object[] paras={id};
        try {
            //BeanHandler<类型>(类型.class)
            //转换为类型的对象
            Comment obj=qr.query(conn,sql,paras,
                    new BeanHandler<Comment>(Comment.class));
            return  obj;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Comment> findAll() {
        String sql="select * from tb_comment";
        Object[] paras={}; //参数
        try {
            //转换器
            //BeanListHandler<类型>(类型.class)
            //将查询结果自动转换为当前类型的List集合
            List<Comment> commentList=
                    qr.query(conn,sql,paras,
                            new BeanListHandler<Comment>(Comment.class));
            return commentList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Comment> findSearch(String keyword) {
        String sql="select * from tb_comment where comment_name like concat(concat('%',?),'%')";
        Object[] paras={keyword};
        try {
            List<Comment> commentList=qr.query(conn,sql,paras,new BeanListHandler<Comment>(Comment.class));
            return commentList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Comment> findPage(int page, int pagesize) {
        String sql="select * from tb_comment " +
                "  limit ?,? ";
        int start=(page-1)*pagesize;
        Object[] paras={start,pagesize};
        try {
            List<Comment> commentList=qr.query(conn,sql,paras,
                    new BeanListHandler<Comment>(Comment.class));
            return  commentList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public long count() {
        String sql="select count(*) from tb_comment";
        Object[] paras={};
        try {
            //ScalarHandler<类型>()
            //返回单一数据类型的数值
            //例如Integer Long Float ...
            long nums=qr.query(conn,sql,paras,
                    new ScalarHandler<Long>());
            return nums;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Map<String, Object>> findByGoodsId(String id) {
        List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
        Map<String, Object> map;
        String sql = "select comment_id,user_name,user_portrait,comment_date,comment_type," +
                "comment_content from tb_user U,tb_comment C , tb_goods G where C.user_id=U.user_id " +
                "and C.goods_id=G.goods_id and G.goods_id=?";
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                map = new HashMap<String, Object>();
                String commentId = rs.getString(1);
                String userName = rs.getString(2);
                String userPortrait = rs.getString(3);
                Date commentDate = rs.getDate(4);
                String commentType = rs.getString(5);
                String commentContent = rs.getString(6);
                map.put("commentId", commentId);
                map.put("userName", userName);
                map.put("userPortrait", userPortrait);
                map.put("commentDate", commentDate);
                map.put("commentType", commentType);
                map.put("commentContent", commentContent);
                mapList.add(map);
            }
            DBUtil.close(rs, ps, conn);
            return mapList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
